#! /usr/bin/perl -w

package DBUtility;

use DBI;
use Constants;
use strict;

our $VERSION = 1.00;

sub new{
	my ($class) = @_;
	my $self = {
		_connection =>undef
	};
	bless $self, $class;
	return $self;
}

sub init{
	my ($self, $host, $db, $username, $password) = @_;
	$self->{_connection} = DBI->connect("DBI:mysql:database=$db;host=$host", "$username", "$password", {'RaiseError' => 1});
	return $FAILURE unless defined($self->{_connection});
	return $SUCCESS;
}

sub destroy{
	my ($self) = @_;
	return $FAILURE unless defined($self->{_connection});
	$self->{_connection}->disconnect();
}

sub add_record{
	my ($self, $table, $hashref) = @_;
	return $FAILURE unless defined($self->{_connection});

	my %hash = %$hashref;
	my ($keystr, $valuestr) = (join(", ", (keys %hash) ), join(", ", (values %hash)));

	my $rows = $self->{_connection}->do("INSERT INTO $table ($keystr) VALUES ($valuestr)");

	return $FAILURE if(!defined($rows));
	return $SUCCESS;
}

sub update_record {
	my ($self, $table, $hashref, $condition) = @_;
	return $FAILURE unless defined($self->{_connection});

	my %hash = %$hashref;
	my @pairs = ();

	foreach my $key (keys %hash) {
		push(@pairs, "$key = $hash{$key}");
	}

	my $update_str = join(", ", @pairs);

	if(defined($condition)) {
		my $rows = $self->{_connection}->do("UPDATE $table SET $update_str WHERE $condition");
		return $FAILURE if(!defined($rows));
		return $SUCCESS;
	} else {
		my $rows = $self->{_connection}->do("UPDATE $table SET $update_str");
		return $FAILURE if(!defined($rows));
		return $SUCCESS;		
	}
}

sub get_connection {
	my ($self) = @_;
	return $self->{_connection};
}

sub execute{
	my ($self, $statement) = @_;
	return $FAILURE unless defined($self->{_connection});

	my $sqr = $self->{_connection}->prepare($statement);
	return $FAILURE if(!defined($sqr->execute()));
	return $SUCCESS;
}

sub query{
   my ($self, $statement) = @_;
   return $FAILURE unless defined($self->{_connection});

   my $hashref = $self->{_connection}->selectrow_hashref($statement);
   return $hashref;
}

sub query_multi{
   my ($self, $statement, $key_field) = @_;
   return $FAILURE unless defined($self->{_connection});

   my $hashref = $self->{_connection}->selectall_hashref($statement, $key_field);
   return $hashref;
}

#delete the record from table runner and capability
sub delete_runner_record{
	my ($self, $runner_identity) = @_;
	return $FAILURE unless defined($runner_identity);

	eval {	
		my $ridref = $self->query("SELECT id FROM runners WHERE identity_str = $runner_identity");
		
		$self->execute("DELETE FROM capabilities WHERE rid = $ridref->{id}");
		$self->execute("DELETE FROM runners WHERE identity_str = $runner_identity");
	};

	if($@){
		return $FAILURE;
	} else {
		return $SUCCESS;
	}
}

sub get_last_insert_id{
	my ($self, $table, $column) = @_;
	return $self->{_connection}->last_insert_id(undef, undef, $table, $column);
}
